package migu.java2excel.code;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import javax.annotation.Resource;
import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import sun.misc.BASE64Decoder;

import com.cmsz.upay.util.ExportUtil;

import migu.java2excel.entity.Department;

public class ExportExcelServiceImpl

	/**
	 * 按时间统计维度导出
	 * @param bean
	 * @param imgName
	 * @param titles
	 * @param outputStream
	 * @throws IOException
	 */
	public void export0(Department department,String imgName, String[] titles, ServletOutputStream outputStream) throws IOException{
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet("按时间统计维度导出");
	//	 this.fillImg(sheet, workBook, imgName);
		 
//		 ExportUtil exportUtil = new ExportUtil(workBook, sheet);  
//	        XSSFCellStyle headStyle = exportUtil.getHeadStyle();  
//	        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle(); 
	        
	        // 构建表头  
	        XSSFRow headRow = sheet.createRow(0);  
	        XSSFCell cell = null;  
	        for (int i = 0; i < titles.length; i++)  
	        {  
	            cell = headRow.createCell(i);  
//	            cell.setCellStyle(headStyle);  
	            cell.setCellValue(titles[i]);  
	        } 
	        		// 构建表体数据  
	                XSSFRow bodyRow = sheet.createRow(1);  
	                cell = bodyRow.createCell(0);  
//	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(department.getName());
	                
	                cell = bodyRow.createCell(1);
	                cell.setCellValue(department.getAddress());
	                
	                cell = bodyRow.createCell(2);
	                cell.setCellValue(department.getBuildYear());
	                
	                cell = bodyRow.createCell(3);
	                cell.setCellValue(department.getIsListed());
	        try  
	        {  
	            workBook.write(outputStream);  
	            outputStream.flush();  
	            outputStream.close();  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	        finally  
	        {  
	            try  
	            {  
	                outputStream.close();  
	            }  
	            catch (IOException e)  
	            {  
	                e.printStackTrace();  
	            }  
	        }   
	}
	
	/**
	 * 按省份日期综合统计维度导出
	 * @param bean
	 * @param imgName
	 * @param titles
	 * @param outputStream
	 * @throws IOException
	 */
	public void export1(QueryInfoBean bean,String imgName, String[] titles, ServletOutputStream outputStream) throws IOException{
		List<UpayRechargePoBean> list =UpayRechargeModelInfoNonePageServiceImpl.getUpayRechargeModelByProvinceNonePage(bean);
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet("按省份日期综合统计维度导出");
	//	 this.fillImg(sheet, workBook, imgName);
		 
		 ExportUtil exportUtil = new ExportUtil(workBook, sheet);
	        XSSFCellStyle headStyle = exportUtil.getHeadStyle();  
	        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle(); 
	        
	        // 构建表头  
	        XSSFRow headRow = sheet.createRow(0);  
	        XSSFCell cell = null;  
	        for (int i = 0; i < titles.length; i++)  
	        {  
	            cell = headRow.createCell(i);  
	            cell.setCellStyle(headStyle);  
	            cell.setCellValue(titles[i]);  
	        } 
	        
	     // 构建表体数据  
	        if (list != null && list.size() > 0)  
	        {  
	            for (int j = 0; j < list.size(); j++)  
	            {  
	//  String[] titles = { "账期时间", "省份", "业务线" , "渠道" , "总充值笔数" , "总充值金额" , "总订单金额" };  
	                  
	                XSSFRow bodyRow = sheet.createRow(j + 1);  
	                UpayRechargePoBean upayRechargeModelInfo = list.get(j);  
	  
	                cell = bodyRow.createCell(0);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTime());  
	  
	                cell = bodyRow.createCell(1);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getProvinceName());  
	  
	                cell = bodyRow.createCell(2);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getBusinessLine()); 
	                
	                cell = bodyRow.createCell(3);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getChannel());  
	                
	             
	                cell = bodyRow.createCell(4);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTotalTopUpPennum());  
	                
	                cell = bodyRow.createCell(5);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTotalTopUpMoney());  
	                
	                cell = bodyRow.createCell(6);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTotalOrderMoney());  
	                
	            }  
	        }  
	        try  
	        {  
	            workBook.write(outputStream);  
	            outputStream.flush();  
	            outputStream.close();  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	        finally  
	        {  
	            try  
	            {  
	                outputStream.close();  
	            }  
	            catch (IOException e)  
	            {  
	                e.printStackTrace();  
	            }  
	        }   
	}
	
	/**
	 * 按省份日期分开统计维度导出
	 * @param bean
	 * @param imgName
	 * @param titles
	 * @param outputStream
	 * @throws IOException
	 */
	public void export2(QueryInfoBean bean,String imgName, String[] titles, ServletOutputStream outputStream) throws IOException{
	
		List<UpayRechargePoBean> list =UpayRechargeModelInfoNonePageServiceImpl.getUpayRechargeModelByProvinceSettledateNonePage(bean);
		
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet("按省份日期分开统计维度导出");
	//	 this.fillImg(sheet, workBook, imgName);
		 
		 ExportUtil exportUtil = new ExportUtil(workBook, sheet);  
	        XSSFCellStyle headStyle = exportUtil.getHeadStyle();  
	        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle(); 
	        
	        // 构建表头  
	        XSSFRow headRow = sheet.createRow(0);  
	        XSSFCell cell = null;  
	        for (int i = 0; i < titles.length; i++)  
	        {  
	            cell = headRow.createCell(i);  
	            cell.setCellStyle(headStyle);  
	            cell.setCellValue(titles[i]);  
	        } 
	        
	     // 构建表体数据  
	        if (list != null && list.size() > 0)  
	        {  
	            for (int j = 0; j < list.size(); j++)  
	            {  
	//  String[] titles = { "账期时间", "省份", "业务线" , "渠道" , "总充值笔数" , "总充值金额" , "总订单金额" };  
	                  
	                XSSFRow bodyRow = sheet.createRow(j + 1);  
	                UpayRechargePoBean upayRechargeModelInfo = list.get(j);  

	                cell = bodyRow.createCell(0);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTime());  
	  
	                cell = bodyRow.createCell(1);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getProvinceName());  
	  
	                cell = bodyRow.createCell(2);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getBusinessLine()); 
	                
	                cell = bodyRow.createCell(3);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getChannel());  
	                
	             
	                cell = bodyRow.createCell(4);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTotalTopUpPennum());  
	                
	                cell = bodyRow.createCell(5);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTotalTopUpMoney());  
	                
	                cell = bodyRow.createCell(6);  
	                cell.setCellStyle(bodyStyle);  
	                cell.setCellValue(upayRechargeModelInfo.getTotalOrderMoney());  
	                
	            }  
	        }  
	        try  
	        {  
	            workBook.write(outputStream);  
	            outputStream.flush();  
	            outputStream.close();  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	        finally  
	        {  
	            try  
	            {  
	                outputStream.close();  
	            }  
	            catch (IOException e)  
	            {  
	                e.printStackTrace();  
	            }  
	        }   
	}
	
	
	public void exportChart(String disposite,String imgName, ServletOutputStream outputStream) throws IOException{
		
		if("0".equals(disposite)){
			XSSFWorkbook workBook = new XSSFWorkbook();
			XSSFSheet sheet = workBook.createSheet("按时间统计维度导出");
			
			this.fillImg(sheet, workBook, imgName);
			
			try  
	        {  
	            workBook.write(outputStream);  
	            outputStream.flush();  
	            outputStream.close();  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	        finally  
	        {  
	            try  
	            {  
	                outputStream.close();  
	            }  
	            catch (IOException e)  
	            {  
	                e.printStackTrace();  
	            }  
	        }
		}
		else{
			
			XSSFWorkbook workBook = new XSSFWorkbook();
			XSSFSheet sheet = workBook.createSheet("按省份统计维度导出");
			
			this.fillImg(sheet, workBook, imgName);
			
			try  
	        {  
	            workBook.write(outputStream);  
	            outputStream.flush();  
	            outputStream.close();  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	        finally  
	        {  
	            try  
	            {  
	                outputStream.close();  
	            }  
	            catch (IOException e)  
	            {  
	                e.printStackTrace();  
	            }  
	        }
		}
		
	}
	public void  fillImg(XSSFSheet sheet,XSSFWorkbook wb,String imageName) throws IOException{
		 XSSFClientAnchor anchor = null;
         BufferedImage bufferImg = null;
         XSSFDrawing patri = sheet.createDrawingPatriarch();
         
         ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
         
         bufferImg = ImageIO.read(new File(imageName));
         
         ImageIO.write(bufferImg, "PNG", byteArrayOut);
         anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 8,
                 5, (short)17, 20);
    
         patri.createPicture(anchor, wb.addPicture(
                 byteArrayOut.toByteArray(),
                 HSSFWorkbook.PICTURE_TYPE_PNG));
         
	}
	
	public String createImg(HttpServletRequest request,String imageData) throws IOException{
		
		  String io = imageData.split(",")[1];
		  byte[] buffer = new BASE64Decoder().decodeBuffer(io);
		  String localdir = request.getSession().getServletContext().getRealPath("img");
		  String imageName =localdir+ "/img.jpg";
		  
		  // 生成本地目录
			 File imgFile  = new File(localdir);
			 if(!imgFile.exists()){  
				 imgFile.mkdirs();  
		        } 
			 
			 OutputStream out = new FileOutputStream(new File(imageName));
	          out.write(buffer);
	          out.flush();
	          out.close();
		return imageName;
	}
	@Override
	public void exportTemplete(HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		ServletOutputStream outputStream = response.getOutputStream();
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet81 = workBook.createSheet("第三方电商");
		XSSFSheet sheet0055 = workBook.createSheet("移动商城");
		XSSFSheet sheet06 = workBook.createSheet("银行总对总");
		
		String[] titles06 = { "业务线" , "省代码","账期",  "订单金额" , "充值金额" ,"业务量","银行编码","渠道" , "二级返回码","交易状态"  };  
		String[] titles81 = { "业务线" , "省代码","账期",  "订单金额" , "充值金额" ,"业务量","产品编码","渠道" , "二级返回码","交易状态"  };  
		String[] titles0055 = { "业务线" , "省代码","账期",  "订单金额" , "充值金额" ,"业务量","支付机构","渠道" , "二级返回码","交易状态"  };  

		  // 构建表头  
        XSSFRow headRow81 = sheet81.createRow(0);  
        XSSFCell cell81 = null;  
        for (int i = 0; i < titles81.length; i++)  
        {  
            cell81 = headRow81.createCell(i);  
           // cell.setCellStyle(headStyle);  
            cell81.setCellValue(titles81[i]);  
        } 
		
        XSSFRow headRow0055 = sheet0055.createRow(0);  
        XSSFCell cell0055 = null;  
        for (int i = 0; i < titles0055.length; i++)  
        {  
            cell0055 = headRow0055.createCell(i);  
           // cell.setCellStyle(headStyle);  
            cell0055.setCellValue(titles0055[i]);  
        } 
        
        
        XSSFRow headRow06 = sheet06.createRow(0);  
        XSSFCell cell06 = null;  
        for (int i = 0; i < titles06.length; i++)  
        {  
            cell06 = headRow06.createCell(i);  
           // cell.setCellStyle(headStyle);  
            cell06.setCellValue(titles06[i]);  
        } 
        
        try  
        {  
            workBook.write(outputStream);  
            outputStream.flush();  
            outputStream.close();  
        }  
        catch (IOException e)  
        {  
            e.printStackTrace();  
        }  
        finally  
        {  
            try  
            {  
                outputStream.close();  
            }  
            catch (IOException e)  
            {  
                e.printStackTrace();  
            }  
        }   
        
	}
	
}
